﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMSX_manual_insert_server]
#-- Purpose:		Manually adds a new server to the list of servers to inventory
#--	Last Update:	04/28/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMSX_manual_insert_server]
(
	@ssd_server_name				varchar(60),
	@ssd_deploy_msx_backup			bit								= NULL,
	@ssd_deploy_msx_index			bit								= NULL,
	@ssd_is_mission_critical		bit								= 0,
	@ssd_description				varchar(1000)					= NULL,
	@ssd_cluster_name				varchar(20)						= NULL
)
AS

DECLARE @similar_name varchar(60), @app_id int

IF EXISTS (	SELECT	TOP 1 [ssd_server_name]
			FROM	[dbo].[tblMSX_server_discovery] (NOLOCK)
			WHERE	[ssd_is_disabled] = 0
					AND [ssd_server_name] = @ssd_server_name
		  )
  BEGIN
	RAISERROR('This server has already been added and cannot be added a second time.', 16, 1)
	RETURN -1
  END

--- Lookup the application id for the given description
IF @ssd_description IS NOT NULL
  BEGIN
	SELECT		@app_id = [app_id]
	FROM		[dbo].[tblMSX_application]
	WHERE		[app_name] = @ssd_description

	IF @app_id IS NULL
	  BEGIN
		INSERT INTO [dbo].[tblMSX_application] ([app_name]) VALUES (@ssd_description)
		SET @app_id = SCOPE_IDENTITY()
	  END
  END

--- This currently fits my local work situation; need to define a more dynamic process - regex or something
---		Server name format ENV#-NAMEDB#
ELSE IF @ssd_description IS NULL
  BEGIN
	SELECT	@similar_name = SUBSTRING(@ssd_server_name, CHARINDEX('-', @ssd_server_name, 1) + 1, LEN(@ssd_server_name))

	SELECT	@app_id = (
			SELECT		TOP 1
						MIN(app.[app_id])
			FROM		[dbo].[tblMSX_server_discovery] ssd
			LEFT JOIN	[dbo].[tblMSX_application] app ON ssd.[ssd_app_id] = app.[app_id]
			WHERE		[ssd_server_name] LIKE '%' + (CASE	WHEN CHARINDEX('DB', @similar_name, 1) = 0 THEN NULL
												ELSE SUBSTRING(@similar_name, 1, CHARINDEX('DB', @similar_name, 1) - 1)
												END) + '%'
						AND app.[app_name] IS NOT NULL
			GROUP BY	app.[app_name]
			)
  END

IF @ssd_deploy_msx_backup IS NOT NULL AND @ssd_deploy_msx_index IS NOT NULL
  BEGIN
	INSERT INTO [dbo].[tblMSX_server_discovery]
	(
		ssd_server_name,
		ssd_deploy_msx_backup,
		ssd_deploy_msx_index,
		ssd_last_update,
		ssd_is_mission_critical,
		ssd_cluster_name,
		[ssd_app_id]
	)
	VALUES
	(
		@ssd_server_name,
		@ssd_deploy_msx_backup,
		@ssd_deploy_msx_index,
		GETDATE(),
		@ssd_is_mission_critical,
		@ssd_cluster_name,
		@app_id
	)
  END
ELSE IF @ssd_deploy_msx_backup IS NOT NULL
  BEGIN
	INSERT INTO [dbo].[tblMSX_server_discovery]
	(
		ssd_server_name,
		ssd_deploy_msx_backup,
		ssd_last_update,
		ssd_is_mission_critical,
		ssd_cluster_name,
		[ssd_app_id]
	)
	VALUES
	(
		@ssd_server_name,
		@ssd_deploy_msx_backup,
		GETDATE(),
		@ssd_is_mission_critical,
		@ssd_cluster_name,
		@app_id
	)
  END
ELSE IF @ssd_deploy_msx_index IS NOT NULL
  BEGIN
	INSERT INTO [dbo].[tblMSX_server_discovery]
	(
		ssd_server_name,
		ssd_deploy_msx_index,
		ssd_last_update,
		ssd_is_mission_critical,
		ssd_cluster_name,
		[ssd_app_id]
	)
	VALUES
	(
		@ssd_server_name,
		@ssd_deploy_msx_index,
		GETDATE(),
		@ssd_is_mission_critical,
		@ssd_cluster_name,
		@app_id
	)
  END
ELSE
  BEGIN
	INSERT INTO [dbo].[tblMSX_server_discovery]
	(
		ssd_server_name,
		ssd_last_update,
		ssd_is_mission_critical,
		ssd_cluster_name,
		[ssd_app_id]
	)
	VALUES
	(
		@ssd_server_name,
		GETDATE(),
		@ssd_is_mission_critical,
		@ssd_cluster_name,
		@app_id
	)
  END

RETURN SCOPE_IDENTITY()